//
//  DBManager.m
//  Alarm
//
//  Created by apple on 2025/8/20.
//

#import "DBManager.h"

@interface DBManager ()
@property (nonatomic, strong) dispatch_queue_t databaseQueue; // 数据库操作串行队列
@property (nonatomic, assign) sqlite3 *database;             // 数据库句柄
@end

@implementation DBManager

#pragma mark - 单例初始化

+ (instancetype)sharedManager {
    static DBManager *sharedInstance = nil;
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        sharedInstance = [[self alloc] init];
        [sharedInstance setupDatabaseQueue];
    });
    return sharedInstance;
}

- (void)setupDatabaseQueue {
    // 创建一个串行队列，所有数据库操作都在这个队列中执行
    _databaseQueue = dispatch_queue_create("com.yourcompany.yourapp.databasequeue", DISPATCH_QUEUE_SERIAL);
    
    // 在队列中同步执行数据库打开操作，确保初始化完成
    dispatch_sync(_databaseQueue, ^{
        [self openDatabase];
        [self createTable];
    });
}


- (NSString *)getDatabasePath {
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths firstObject];
    return [documentsDirectory stringByAppendingPathComponent:@"mydatabase.sqlite"];
}

- (void)openDatabase {
    NSString *dbPath = [self getDatabasePath];
    
    // sqlite3_open 打开数据库，如果不存在则创建
    int result = sqlite3_open([dbPath UTF8String], &_database);
    
    if (result != SQLITE_OK) {
        NSLog(@"Failed to open database: %s", sqlite3_errmsg(_database));
        sqlite3_close(_database);
    } else {
        NSLog(@"Database opened/created successfully at: %@", dbPath);
    }
}

- (void)createTable {
    // 编写 SQL 语句
    NSString *sqlStatement =  @"CREATE TABLE IF NOT EXISTS 't_alarm' (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, tag TEXT, alarm_id TEXT, title TEXT, body TEXT, date TEXT, hour TEXT, minute TEXT, repeating TEXT)";
    char *error;
    
    // sqlite3_exec 用于执行不返回数据的SQL语句（CREATE, INSERT, UPDATE, DELETE）
    int result = sqlite3_exec(_database, sqlStatement.UTF8String, NULL, NULL, &error);
    
    if (result != SQLITE_OK) {
        NSLog(@"Failed to create table: %s", error);
        sqlite3_free(error); // 释放错误消息占用的内存
    } else {
        NSLog(@"Table created successfully.");
    }
}


// 插入数据
- (void) insertData:(NSDictionary *)params completion:(CompletionBlock _Nullable)completion {
        
    NSString *tag = [params objectForKey:@"tag"];
    NSString *alarm_id = [params objectForKey:@"alarm_id"];
    NSString *title = [params objectForKey:@"title"];
    NSString *body = [params objectForKey:@"body"];
    NSString *date = [params objectForKey:@"date"];
    NSString *hour = [NSString stringWithFormat:@"%@", [params objectForKey:@"hour"]];
    NSString *minute = [NSString stringWithFormat:@"%@", [params objectForKey:@"minute"]];
    NSString *repeating = [NSString stringWithFormat:@"%@", [params objectForKey:@"repeating"]];
    
    __weak typeof(self) weakSelf = self;
    [self queryDataTag:tag withAlarmId:alarm_id completion:^(BOOL success, NSArray * _Nonnull resultArr, NSString * _Nullable msg) {
        if(resultArr.count > 0){
            completion(NO, @"不允许重复设置闹钟");
            return;
        }else{
            
            // 使用 ? 作为占位符，防止 SQL 注入
            const char *sqlStatement = "INSERT INTO 't_alarm' (tag, alarm_id, title, body, date, hour, minute, repeating) VALUES (?,?,?,?,?,?,?,?)";
            sqlite3_stmt *statement;
            
            // 编译 SQL 语句
            int result = sqlite3_prepare_v2(weakSelf.database, sqlStatement, -1, &statement, NULL);
            
            if (result == SQLITE_OK) {
                // 绑定参数到占位符
                sqlite3_bind_text(statement, 1, [tag UTF8String], -1, SQLITE_TRANSIENT);
                sqlite3_bind_text(statement, 2, [alarm_id UTF8String], -1, SQLITE_TRANSIENT);
                sqlite3_bind_text(statement, 3, [title UTF8String], -1, SQLITE_TRANSIENT);
                sqlite3_bind_text(statement, 4, [body UTF8String], -1, SQLITE_TRANSIENT);
                sqlite3_bind_text(statement, 5, [date UTF8String], -1, SQLITE_TRANSIENT);
                sqlite3_bind_text(statement, 6, [hour UTF8String], -1, SQLITE_TRANSIENT);
                sqlite3_bind_text(statement, 7, [minute UTF8String], -1, SQLITE_TRANSIENT);
                sqlite3_bind_text(statement, 8, [repeating UTF8String], -1, SQLITE_TRANSIENT);

                // 执行语句
                if (sqlite3_step(statement) == SQLITE_DONE) {
                    NSLog(@"Data inserted successfully.");
                    completion(YES, @"Data inserted successfully.");
                } else {
                    NSLog(@"Failed to insert data.");
                    completion(NO, @"Failed to insert data.");
                }
            } else {
                NSLog(@"Failed to prepare statement: %s", sqlite3_errmsg(weakSelf.database));
                completion(NO, @"Failed to prepare statement.");
            }
            
            // 释放编译好的语句，防止内存泄漏
            sqlite3_finalize(statement);
                        
        }
                
    }];
    
}


// 更新数据
- (void) updateData:(NSDictionary *)params completion:(CompletionBlock _Nullable)completion{
    
    
}

// 删除数据
- (void) deleteDataTag:(NSString *)tag withAlarmId:(NSString *)alarm_id completion:(CompletionBlock _Nullable)completion{
    
    char *error;
    NSString *sqlStatement = [NSString stringWithFormat:@"delete from 't_alarm'"];
    if(![self isNullWithStr:tag] && ![self isNullWithStr:alarm_id]){
        sqlStatement = [NSString stringWithFormat:@"delete from 't_alarm' where tag='%@' AND alarm_id='%@'", tag, alarm_id];
    }else if(![self isNullWithStr:tag] && [self isNullWithStr:alarm_id]){
        sqlStatement = [NSString stringWithFormat:@"delete from 't_alarm' where tag='%@'", tag];
    }
   
    int result = sqlite3_exec(_database, sqlStatement.UTF8String, nil, nil, &error);

    if (result == SQLITE_OK) {
        NSLog(@"[DB Delete] : OK");
        completion(YES, @"delete successfully.");
    }else {
        completion(NO, @"delete failed.");
        NSLog(@"error = %s",error);
    }
    
}



// 查找数据
- (void) queryDataTag:(NSString *)tag withAlarmId:(NSString *)alarm_id completion:(QueryResultBlock _Nullable)completion{
    
    
    NSString *sqlStatement = [NSString stringWithFormat:@"SELECT id, tag, alarm_id, title, body, date, hour, minute, repeating FROM 't_alarm'"];
    
    if(![self isNullWithStr:tag] && ![self isNullWithStr:alarm_id]){
        sqlStatement = [NSString stringWithFormat:@"SELECT id, tag, alarm_id, title, body, date, hour, minute, repeating FROM 't_alarm' WHERE tag='%@' AND alarm_id='%@'", tag, alarm_id];
    }else if(![self isNullWithStr:tag] && [self isNullWithStr:alarm_id]){
        sqlStatement = [NSString stringWithFormat:@"SELECT id, tag, alarm_id, title, body, date, hour, minute, repeating FROM 't_alarm' WHERE tag='%@'", tag];
    }
    
    sqlite3_stmt *statement;
    
    int result = sqlite3_prepare_v2(_database, sqlStatement.UTF8String, -1, &statement, NULL);
    
    if (result == SQLITE_OK) {
        
        NSMutableArray *resultArr = [NSMutableArray array];
        // 循环遍历结果集的每一行
        while (sqlite3_step(statement) == SQLITE_ROW) {
            
            // 从当前行中按列索引取出数据            
            int aId = sqlite3_column_int(statement, 0);
            
            char * tagChar = (char *)sqlite3_column_text(statement, 1);
            char * alarm_idChar = (char *)sqlite3_column_text(statement, 2);
            char * titleChar = (char *)sqlite3_column_text(statement, 3);
            char * bodyChar = (char *)sqlite3_column_text(statement, 4);
            char * dateChar = (char *)sqlite3_column_text(statement, 5);
            char * hourChar = (char *)sqlite3_column_text(statement, 6);
            char * minuteChar = (char *)sqlite3_column_text(statement, 7);
            char * repeatingChar = (char *)sqlite3_column_text(statement, 8);

            NSString *tag = @"";
            NSString *alarm_id = @"";
            NSString *title = @"";
            NSString *body = @"";
            NSString *date = @"";
            NSString *hour = @"";
            NSString *minute = @"";
            NSString *repeating = @"";

            if(tagChar != NULL)
                tag = [NSString stringWithUTF8String: tagChar];
            if(alarm_idChar != NULL)
                alarm_id = [NSString stringWithUTF8String: alarm_idChar];
            if(titleChar != NULL)
                title = [NSString stringWithUTF8String: titleChar];
            if(tagChar != NULL)
                body = [NSString stringWithUTF8String: bodyChar];
            if(dateChar != NULL)
                date = [NSString stringWithUTF8String: dateChar];
            if(hourChar != NULL)
                hour = [NSString stringWithUTF8String: hourChar];
            if(minuteChar != NULL)
                minute = [NSString stringWithUTF8String: minuteChar];
            if(repeatingChar != NULL)
                repeating = [NSString stringWithUTF8String: repeatingChar];
            
            
            NSMutableDictionary *dic = [NSMutableDictionary dictionary];
            [dic setValue:@(aId) forKey:@"id"];
            if(![self isNullWithStr:tag])
                [dic setValue:tag forKey:@"tag"];
            if(![self isNullWithStr:alarm_id])
                [dic setValue:alarm_id forKey:@"alarm_id"];
            if(![self isNullWithStr:title])
                [dic setValue:title forKey:@"title"];
            if(![self isNullWithStr:body])
                [dic setValue:body forKey:@"body"];
            if(![self isNullWithStr:date])
                [dic setValue:date forKey:@"date"];
            if(![self isNullWithStr:hour])
                [dic setValue:@([hour intValue]) forKey:@"hour"];
            if(![self isNullWithStr:minute])
                [dic setValue:@([minute intValue]) forKey:@"minute"];
            if(![self isNullWithStr:repeating])
                [dic setValue:@([repeating intValue]) forKey:@"repeating"];
            
            [resultArr addObject:dic];

        }
        
        completion(YES, resultArr, @"获取数据成功");
        
    } else {
        NSLog(@"Failed to prepare statement: %s", sqlite3_errmsg(_database));
        
        completion(NO, @[], @"获取数据失败");
    }
    
    sqlite3_finalize(statement);
}


- (void)dealloc {
    if (_database) {
        dispatch_sync(_databaseQueue, ^{
            sqlite3_close(_database);
            _database = NULL;
        });
    }
}



// 判断是否为空
- (BOOL )isNullWithStr:(NSString *)Str {

    if ([Str isEqual:[NSNull null]]) {
        return YES;
    }
    
    if (![Str isKindOfClass:[NSString class]]) {
        return YES;
    }
    
    if (Str == nil){
        return YES;
    }
    
    if (Str == NULL){
        return YES;
    }
    
    if ([Str isKindOfClass:[NSNull class]]) {
        return YES;
    }
    
    if ([Str isEqualToString:@"(null)"]) {
        return YES;
    }
    
    if ([Str isEqualToString:@"<null>"]) {
        return YES;
    }
    
    if ([Str isEqualToString:@""]) {
        return YES;
    }
    if ([Str isEqualToString:@" "]) {
        return YES;
    }
    if ([[Str stringByTrimmingCharactersInSet:[NSCharacterSet whitespaceCharacterSet]] length] == 0) {
        return YES;
    }
    return NO;
}


@end
